﻿/*
* Sys_SMS_reports.cs
*
* 功 能： N/A
* 类 名： Sys_SMS_reports
*
* Ver    变更日期             负责人     变更内容
* ───────────────────────────────────
* V1.0  2015/8/24 11:52:37    黄润伟    
*
* Copyright (c) 2015 www.xhdcrm.com  www.xhdoa.com. All rights reserved.
*┌──────────────────────────────────┐
*│　版权所有：小黄豆                      　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using XHD.DBUtility;//Please add references
namespace XHD.DAL
{
	/// <summary>
	/// 数据访问类:Sys_SMS_reports
	/// </summary>
	public partial class Sys_SMS_reports
	{
		public Sys_SMS_reports()
		{}
		#region  BasicMethod

		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(XHD.Model.Sys_SMS_reports model)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append(" if not exists(select 1 from Sys_SMS_reports where mobile=@mobile and seqID=@seqID) ");
            strSql.Append(" begin ");
			strSql.Append("insert into Sys_SMS_reports(");
			strSql.Append("reportStatus,mobile,submitDate,receiveDate,errorCode,memo,serviceCodeAdd,seqID)");
			strSql.Append(" values (");
			strSql.Append("@reportStatus,@mobile,@submitDate,@receiveDate,@errorCode,@memo,@serviceCodeAdd,@seqID)");
			strSql.Append(";select @@IDENTITY");
            strSql.Append(" end");
			SqlParameter[] parameters = {
					new SqlParameter("@reportStatus", SqlDbType.Int),
					new SqlParameter("@mobile", SqlDbType.VarChar,50),
					new SqlParameter("@submitDate", SqlDbType.VarChar,50),
					new SqlParameter("@receiveDate", SqlDbType.VarChar,50),
					new SqlParameter("@errorCode", SqlDbType.VarChar,50),
					new SqlParameter("@memo", SqlDbType.NVarChar,500),
					new SqlParameter("@serviceCodeAdd", SqlDbType.VarChar,50),
					new SqlParameter("@seqID", SqlDbType.BigInt,4)};
			parameters[0].Value = model.reportStatus;
			parameters[1].Value = model.mobile;
			parameters[2].Value = model.submitDate;
			parameters[3].Value = model.receiveDate;
			parameters[4].Value = model.errorCode;
			parameters[5].Value = model.memo;
			parameters[6].Value = model.serviceCodeAdd;
			parameters[7].Value = model.seqID;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}		
       
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,reportStatus,mobile,submitDate,receiveDate,errorCode,memo,serviceCodeAdd,seqID ");
            strSql.Append(",(select C_name from CRM_Contact where C_mob = Sys_SMS_reports.mobile) as C_name ");
            strSql.Append(" FROM Sys_SMS_reports ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" id,reportStatus,mobile,submitDate,receiveDate,errorCode,memo,serviceCodeAdd,seqID ");
            strSql.Append(",(select C_name from CRM_Contact where C_mob = Sys_SMS_reports.mobile) as C_name ");
            strSql.Append(" FROM Sys_SMS_reports ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total)
		{
			StringBuilder strSql_grid = new StringBuilder();
			StringBuilder strSql_total = new StringBuilder();
			strSql_total.Append(" SELECT COUNT(id) FROM Sys_SMS_reports ");
			strSql_grid.Append("SELECT ");
			strSql_grid.Append("      n,id,reportStatus,mobile,submitDate,receiveDate,errorCode,memo,serviceCodeAdd,seqID ");
            strSql_grid.Append(",(select C_name from CRM_Contact where C_mob = w1.mobile) as C_name ");
            strSql_grid.Append(" FROM ( SELECT id,reportStatus,mobile,submitDate,receiveDate,errorCode,memo,serviceCodeAdd,seqID, ROW_NUMBER() OVER( Order by " + filedOrder + " ) AS n from Sys_SMS_reports");
			if (strWhere.Trim() != "")
			{
				strSql_grid.Append(" WHERE " + strWhere);
				strSql_total.Append(" WHERE " + strWhere);
			}
			strSql_grid.Append("  ) as w1  ");
			strSql_grid.Append("WHERE n BETWEEN " + PageSize * (PageIndex - 1) + " AND " + PageSize * PageIndex);
			strSql_grid.Append(" ORDER BY " + filedOrder );
			Total = DbHelperSQL.Query(strSql_total.ToString()).Tables[0].Rows[0][0].ToString();
			return DbHelperSQL.Query(strSql_grid.ToString());
		 }

		#endregion  BasicMethod
		#region  ExtensionMethod

		#endregion  ExtensionMethod
	}
}

